---
title: "Part 1 Deconstruction Wood Sensitivity Analysis"
subtitle: "Material Weight Conversion using average density from OSU Table 2"
author:
- Andey Nunes, MS
- Research Analyst 2
- Oregon DEQ
date: "`r format(Sys.time(), '%B %d, %Y')`"
output:
   html_document:
    df_print: paged
    toc: yes
    toc_depth: '3'
---

# Deconstruction Materials Weight Calculations

This tool consists of five R Markdown files which, when executed in consecutive order, can be used to determine and report environmental benefits of deconstruction over mechanical demolition of residential single family homes.  

## Purpose and Outputs

Part 1 converts quantities from material inventory reports into quantities that are compatible with the LCA impact factors used to develop net environmental benefits of residential deconstruction projects.

The outputs of this notebook are summarized in the following table:

| Output Name                          | Description                                                     |
|--------------------------------------|-----------------------------------------------------------------|
| `default_material_dimensions` table | table of the default weight and dimensions for materials where information was either missing or incomplete |
| `weight_calc` data table | complete record of intermediate variable steps used to convert quantities reported in "deconstruction_receipts_summary.xlsx" converted to "kg". Useful for data QA/QC check |
| `missing_deconMaterialName.csv` | list of material items reported that do not have a material category to assign LCA impacts |
| `missing_quantity_data.csv` | contractor reported data with missing or incomplete information, where no default or assumed values could be used |
| `decon_material_weight` data table | cleaned data from the `weight_calc` table, which will be used to develop line item specific impacts in the successive R Markdown documents |


## Required Data Sources and File Locations

Sources of data for this analysis include:

* Receipts from dropbox and recycling haulers;
* Handwritten sale or donation slips;
* Salvage retailer inventory forms and lists.

Typically, materials data from receipts and inventory sources must be collected, entered into a spreadsheet where each row is a separate material line item with column variables specified for R compatibility, and formatted into a csv file. An input file template is included with the R project file and the City of Portland 2017 deconstruction data set is also available as an example of a compatible input file. Users who wish to use their own file for analysis are encouraged to add a copy of the spreadsheet in csv format to the R project folder and replace the code references to the City of Portland data file with their file name as directed in the code sections that follow. It is recommended that users avoid coding file directory specifications into the R Markdown files. Additionally, caution is warranted that user's existing global options may impact or override the R project file options. 
  
It is important to note that R is a case sensitive language, so capitalization and spaces are common sources of incompatibility of input files with the tool scripts. For more information on utilizing R Markdown files visit https://rmarkdown.rstudio.com/authoring_quick_tour.html


### New input files
When creating a new input file for analysis, starting with the template included is advised. Column names must match exactly if they are to be included, and several fields have specific entry options for proper data processing and handling. Figure 1 is an example of a properly formatted input file

![**FIGURE 1: Example input file format**](data/input_file_example.png) 

The input file field `deconMaterialName` is a vector of character strings that are used to map the data observation quantity to the LCA impact factor data. This value is determined and assigned in the data entry phase and requires some attention to detail. See Appendix for the current list of options for completing this field and assigning material types to new data. Presently there is no automated process for this, users must decide and assign the material category that best fits the material line item. These material categories will be expanded to include more types of building materials in future tool iterations. Before the data summary table is cleaned, missing values will be summarized and handled according to what is not available, therefore it is ok to include data from a deconstruction project that doesn't yet have an appropriate `deconMaterialName` that can be assigned.
 
Additionally, `dimensions` is a crucial user input field that contains the information needed to calculate material volumes that will be converted to weight. The format requires entry of the product **actual** dimensions (as distinguished from the name or *nominal* product dimensions) separated by a lower case "x",  with the associated units recorded in the `dimensional_units` column. This is one of the most important features of the data entry because the weight of framing and other lumber is calculated based on the lumber density and the calculated volume, which is determined by the physical dimensions of the materials. In future iterations of the tool, this process may be automated with a feature for optional manual entry.

For materials with subcategory names in the `deconMaterialName` variable, such as doors, windows, and cabinets, the `description` field should include just the main category material name as this is used for assigning default values during data processing. Other line item `description` entries should be short and contain enough information to determine the material type (`deconMaterialName`), dimensions not specified in the `dimensions` column such as plywood other sheet material thickness, wood species, or other details needed to convert from the quantity of the material reported in the receipt or inventory list to a weight value in kilograms. Additional detail about material line items should be entered in the `notes` column as this is kept only for documentation purposes and will only used for quality control.

##### Notebook Setup

In order to execute all five R Markdown files, users will need to have R and RStudio installed with the *tidyverse* packages in addition to the following packages available from CRAN: *corrplot*, *ggrepel*, *ggthemes*, *knitr*, *rebus*. More information about R, RStudio, and the packages used to build and deploy the deconstruction tool can be found here: https://www.rstudio.com/products/rpackages/

Each R Markdown file contains code chunks and narrative text which automates the code execution and document formatting. Code can be executed one chunk at a time, or the appropriate files names can be adapted in the code and the entire document rendered as an HTML (recommended for best formatting), pdf,  or word document.



```{r notebook setup}
packages <- c("knitr", "rebus", "tidyverse")
lapply(packages, require, character.only = T)
options(digits = 3)
options(scipen = 999)
```

Data exploration begins with loading the csv files into the R Notebook and listing descriptions of the various features and fields in the data. Before diving into the data, a few reference tables will be generated, printed in an appendix, and saved for future use. 

# Conversion Factors and Default Values

### Definitions and Abbreviations

First we need to define the abbreviations that may be used throughout the project documents. These are summarized in Appendix table A1

```{r definitions}
definitions <- tibble(
   abbreviations = c("bd_ft", "lin_ft","sq_in", "sq_ft", "cu_ft", "cu_m", "kg", "lbs","gal", "tons", "m_tons", "MJ", "kg_CO2e", "googUC", "awcEPD", "ahacAHG", "OSB (or osb)", "CVG", "RC", "DF", "T&G"), 
   unit_name = c("board feet", "lineal feet", "square inches", "square feet", "cubic feet", "cubic meters", "kilograms", "pounds", "US gallons", "US tons", "metric tons", "mega joules", "kilograms carbon dioxide equivalent", "Google Unit Converter tool", "American Wood Council environmental product declaration", "American Hardwood Export Council American Hardwood Guide", "oriented strand board wood product", "clear vertical grain wood", "rough cut lumber", "douglas fir product", "tongue-and-groove cut wood finish product")
   )

```

## Unit Conversion Factors

Next a collection of unit conversions and their sources is given the unit conversions table (see appendix A2). 

```{r unit conversions}
unit_conversions <- tibble(
   application = c("US area conversion", "US old growth lumber volume conversion", "US metric volume conversion", "metric softwood density", "metric hardwood density", "US metric weight conversion", "US short tons conversion", "US volume conversion"),
   value = c(144, 12, 35.3147, 530.70, 770, 2.2046, 907.185, 7.48052),
   value_unit = c("sq_in", "bd_ft", "cu_ft", "kg", "kg", "kg", "kg", "gal"),
   per = c("sq_ft", "cu_ft", "cu_m", "cu_m", "cu_m", "lbs", "US_ton", "cu_ft" ),
   info_source = c("googUC", "googUC", "googUC", "OSUtable2average", "ahacAHG", "googUC","googUC", "googUC")
)

# for convenience, add a couple more conversions that are derived from the values in the unit_conversions table defined above
# first add US volume (in cubic feet) conversions for the wood density factors (keeping in kilograms to match impact factor `declaredUnit`)
# the `dplyr::add_row()` function does not like to do calculations for assigning values to the fields, so initially the value is set to 0 then subsequent code lines do the math
unit_conversions <- unit_conversions %>%
   add_row(application = "US metric softwood density", 
           value = 0,
           value_unit = "kg",
           per = "cu_ft",
           info_source = "calculated"
   ) %>%
   add_row(application = "US metric hardwood density", 
           value = 0,
           value_unit = "kg",
           per = "cu_ft",
           info_source = "calculated"
   )
# US metric softwood density value = metric softwood density value (kg per cu_m) / US metric volume conversion (cu_ft per cu_m)
unit_conversions[9,2] <- unit_conversions[4,2]/unit_conversions[3,2]
# US metric hardwood density value = metric hardwood density value (kg per cu_m) / US metric volume conversion (cu_ft per cu_m)
unit_conversions[10,2] <- unit_conversions[5,2]/unit_conversions[3,2]

# create the value_reciprocal column
unit_conversions$value_reciprocal <- 1/unit_conversions$value

unit_conversions <- unit_conversions[,c(1:4,6,5)]

# named factors needed as numeric classification for use in later mutate call
softwood_density_factor <- as.numeric(unit_conversions[9,2])
hardwood_density_factor <- as.numeric(unit_conversions[10,2]) 
lbs_to_kg <- as.numeric(unit_conversions[6,5])
short_tons_conversion <- as.numeric(unit_conversions[7,2])
```

## Default Material Dimensions
In some instances, the commonly reported values and units for material items is inadequate for determining the weight of the item. This typically occurs with fixtures such as doors, toilets, and lighting. Where the weight of these items is known or can be measured, that actual value should be used, however, when this is impractical, the following table of default material dimensions will be used to substitute values during the data cleaning routines at the end of this document. 

The default material dimensions match line item `description`s to factors which can then be multiplied by the `listed_quantity` to get the `amount` of the line item material that will be converted to `declaredUnit`s and used to generate impact values.

```{r material default dimensions}
# first generate a data frame from a list of the materials which will be built out 
default_material_dimensions <- tibble(
   description = c("tub", "sink",
                  "tub", "sink", "angle iron", 
                  "toilet","sink", "tile (box)", 
                  "sink", "screen door", "garage door", "security door", "window bars", "metal post", "hardware", "railing", "vent", "gutter", "grab bar", "tub", "corrugated sheetmetal",
                  "carpet",
                  "plywood",
                  "osb",
                  "lath", "flooring (sq_ft)", "porch corbels", "newel post", "banister", "column", "mantel",
                  "flooring (sq_ft)", "bench", "grate",
                  "flooring (sq_ft)", "steps",
                  rep("doors", 3),
                  rep("windows", 3),
                  rep("cabinets", 3),
                  "light fixtures"
                  ),
   deconMaterialName = c(rep("fiberglass tub", 2),
                        rep("cast iron", 3), 
                        rep("ceramics", 3), 
                        rep("steel product", 13),
                        "carpeting",
                        "plywood",
                        "osb",
                        rep("softwood lumber", 7),
                        rep("hardwood lumber", 3),
                        rep("hardwood flooring", 2),
                        "outer door (solid wood)", "inner door (solid wood)", "inner door (hollow wood)",
                        "window (single wood)", "window (double wood)", "window (double vinyl)",
                        "cabinets (lower)", "cabinets (upper short)", "cabinets (upper long)",
                        "light fixture"),
   default_value = c(60, 10, 
                     300, 50, 222.85,
                     100, 20, 50,
                     10, 15, 250, 100, 50, 50, 5, 25, 5, 5, 5, 75, 1,
                     2,
                     3,
                     3.4,
                     18.4, 1/12, 5, 10, 20, 50, 100,
                     1/12, 100, 5,
                     1/12, 1/12*36*12/144, 
                     46.83, 23.26, 18.29,
                     1.6265*12, 2.1897*12, 2.4471*12,
                     15.27, 8.21, 11.08,
                     5
   ),
   default_value_units = c(rep("lbs", 2),
                          rep("lbs", 2), "kg per cu_ft",
                          rep("lbs", 3),
                          rep("lbs", 12), "lbs per sq_ft",
                          "lbs per sq_ft",
                          "lbs per sq_ft per inch thickness",
                          "lbs per sq_ft per inch thickness",
                          "lbs per bundle", "ft thickness", rep("lbs", 5),
                          "ft thickness", rep("lbs", 2),
                          "ft thickness", "cu_ft per 12x36x1_inch step",
                          rep("kg per door", 3),
                          rep("kg per (3'x4' = 12sq_ft) window", 3),
                          rep("kg per lineal ft", 3),
                          "lbs per fixture"
   )
)

write_csv(default_material_dimensions, "output/default_material_dimensions.csv")

```


# Data conversion routines

Before beginning the data cleaning and preparation, the spreadsheet containing the raw data summary must be loaded into the R session. This is point where new users can substitute their own csv file for the `public_use_raw_data_summary.csv` file that is used for the City of Portland report. Simply add the new file to the R project file then in the call to `read_csv()` replace the file name inside the quotes. 

```{r raw data summary import}
# import raw data summary file
raw_data_summary_sheet <- read_csv("data/public_use_raw_data_summary.csv")

# to read in new data, drop the new csv file into the project data folder 
# add a hashtag to comment out the import command above 
# substitute the file path/name in the command below
# then remove the hashtag to uncomment the import command with your new file name
# 
# import new data file
# raw_data_summary_sheet <- read_csv("data/replace with your file name here.csv")


```


The following steps are used to convert from commonly used building material quantity reporting units to calculated weight values in `declaredUnits` that can be multiplied by impact factors for generating the net benefits calculations:

### Step One: Dimensional units conversion

```{r dimensional conversion}

# create rebus objects for character string parsing
separators <- char_class("./ ")

sq_dimensions <- one_or_more(DGT) %R% optional(one_or_more(DGT)) %R% optional(separators) %R% optional(one_or_more(DGT)) %R% optional(one_or_more(DGT)) %R% "x" %R% one_or_more(DGT) %R% optional(one_or_more(DGT)) %R% optional(separators) %R% optional(one_or_more(DGT)) %R% optional(one_or_more(DGT))

# extract dimensions
dimension_calc_extract <- str_extract_all(raw_data_summary_sheet$dimensions, sq_dimensions, simplify = T) %>%
   str_split("x", n = 2, simplify = T)

# create a new data frame using the extraction object
dimension_product <- data.frame(dimension_calc_extract[1:length(raw_data_summary_sheet$dimensions),1:2], stringsAsFactors = F)

# convert vector class and add units
dimension_product[,1] <- as.numeric(dimension_product[,1])
dimension_product[,2] <- as.numeric(dimension_product[,2])
dimension_product$dim_units <- raw_data_summary_sheet$dimensional_units

# multiply dimensions and convert units where necessary
dimension_product <- dimension_product %>%
   mutate(sq_dim = dimension_product[,1] * dimension_product[,2]) %>%
   mutate(sq_dim_units = case_when(
      dim_units == "in" ~ "sq_in",
      dim_units == "ft" ~ "sq_ft")
   ) %>%
   mutate(sq_ft_dim = case_when(
      sq_dim_units == "sq_in" ~ sq_dim/144,
      sq_dim_units == "sq_ft" ~ sq_dim)
   )

# clean up workspace
rm(dimension_calc_extract)
rm(sq_dimensions)
# don't delete dimension_product because it is needed to calculate plywood and osb weight
```


### Step Two: Calculate material units from dimensions

**Create the `per_unit_value` which represents calculated value from dimensions given for the line item `description`:** 

This new `per_unit_value` is the line item quantity in units compatible with the `listed_quantity` values reported by the contractors, and is dependent upon the type of material. To illustrate, the `per_unit_value` for Softwood lumber is the dimensional expression of lumber which can be multiplied by the `listed_quantity` reported by contractors to achieve a consistent cubic foot volume measure. For engineered wood products, contractors reported the number of sheets of plywood or oriented strand board and in some cases, included the sheet dimensions (here thickness is relevant, but default values can be used where data is missing). The `per_unit_value` for these items is the result of calculating the weight per sheet according to either reported or assumed product dimensions.  

The `default_value` represents the assumed conversion from the `listed_units` to weight, with the caveat that many values will need an additional conversion before matching the `impact_units` (which is typically in kilograms).


```{r material dimensions data frame}
# this line preserves the raw data summary and creates an object with column order rearranged to facilitate creating an object whose variable transformations can be read from left to right columnwise    
material_dimensions_calc <- raw_data_summary_sheet %>%
   select(c( "project", "contractor", "house_age", "house_size", "description", "notes", "deconMaterialName", "listed_quantity", "listed_units", "dimensions", "dimensional_units"))

# match up the converted square footage of materials to the raw_data_summary_sheet table; this takes care of most of the lumber line items

material_dimensions_calc <- material_dimensions_calc %>%
   mutate(per_unit_value = case_when(
   .$listed_units == "bd_ft" ~ 1/12,
   .$listed_units == "sq_ft" ~ dimension_product$X1/12,
   .$listed_units == "lin_ft" ~ dimension_product$sq_ft_dim,
   .$description == "cabinets" & .$listed_units == "count" ~ 3,
   .$deconMaterialName == "plywood" ~ dimension_product$sq_dim,
   .$deconMaterialName == "osb" ~ dimension_product$sq_dim,
   .$deconMaterialName == "carpeting" ~ 100,
   .$description == "corrugated sheetmetal" ~ dimension_product$sq_ft_dim,
   .$description == "angle iron" ~  161.05)
   ) %>% 
   mutate(per_unit_value_units = case_when(
   .$listed_units == "bd_ft" ~ "cu_ft_conversion",
   .$listed_units == "sq_ft" ~ "ft thickness",
   .$description == "cabinets" & .$listed_units == "count" ~ "lin_ft",
   .$listed_units == "lin_ft" ~ "sq_ft",
   .$deconMaterialName == "plywood" ~ "sq_ft",
   .$deconMaterialName == "osb" ~ "sq_ft",
   .$deconMaterialName == "carpeting" ~ "assumed_sq_ft_per_roll",
   .$description == "corrugated sheetmetal" ~ "sq_ft",
   .$description == "angle iron" ~ "kg")
   )

# weight calculations table
weight_calc <- left_join(material_dimensions_calc, default_material_dimensions,  by = c("description", "deconMaterialName"))

# clean up workspace, but don't remove dimension product just yet, as it will be needed later to calculate plywood & osb sheet weights
rm(material_dimensions_calc)
```

*Engineered wood products*
For engineered wood products (Plywood and OSB) the default thickness is 1/2 inch, and default sheet size is 32 square feet (4' by 8'). The default material dimension value is `lbs per sq_ft per inch thickness` (which is an equivalent board feet measure), so the `per_unit_value` calculation therefore represents thickness as a percentage of an inch. An example of the calculation for 1 sheet of OSB (4' by 8') at 1/2" thickness would be:

$$ 0.5in * 32 sq ft * 2.75 \frac {lbs}{sqft*in} = 44lbs$$

More work is needed here to convert `per_unit_value` of the plywood and OSB items from the`per_unit_value_unit` in square feet that resulted from multiplying the dimensions to the weight per sheet so that multiplying the contractor reported values in sheets of engineered wood product by the `per_unit_value` will produce a `calculated_quantity` that is the total weight of the sheets of wood product. This code block generates a table which collects the `reported_thickness` and `default_thickness` in inches, the `sq_ft_dim` product dimension in square feet for the line item, and the `default_value` of pounds per square feet per inch of thickness which in turn are used to calculate the `per_unit_value` weight per sheet for that specific engineered wood product line item.

```{r plywood osb conversion}
# which items reported the thickness?
finding_engineered_wood_thickness <- DGT %R% "/" %R% DGT #%R% '" ' 

thickness_calc_extract <- str_extract_all(raw_data_summary_sheet$description, 
                                          finding_engineered_wood_thickness, simplify = T) %>%
                           str_split(separators, n = 2, simplify = T)

thickness <- data.frame(thickness_calc_extract[1:length(raw_data_summary_sheet$description),1:2], stringsAsFactors = F)

thickness <- tibble(as.numeric(thickness[,1]) / as.numeric(thickness[,2]))

thickness$deconMaterialName <- weight_calc$deconMaterialName


# assign the default thickness to the rest of the items where thickness was not reported
thickness <- thickness %>% mutate(default_thickness = case_when(
                              .$deconMaterialName == "osb" ~ 0.5,
                              .$deconMaterialName == "plywood" ~ 0.5)) %>%
                           left_join(default_material_dimensions, by = c("deconMaterialName" = "description")) %>%
                           add_column(dimension_product$sq_ft_dim) %>%
                           select(c(2, 1, 3, 5:7))

names(thickness) <- (c("deconMaterialName", "reported_thickness", "default_thickness", "default_weight", "default_weight_units", "sq_ft_dim"))

# use the equation above to produce a new per_unit_value that gives the weight of a sheet of that product line item.
thickness <- thickness %>% mutate(sheet_weight = ifelse(is.na(thickness$reported_thickness),
                                                      default_thickness * default_weight * sq_ft_dim,
                                                      reported_thickness * default_weight * sq_ft_dim
                           ))

weight_calc <- weight_calc %>% add_column(thickness$sheet_weight, .before = "per_unit_value") %>%
                              rename(sheet_weight = `thickness$sheet_weight`)

weight_calc <- weight_calc %>% mutate(per_unit_value = ifelse(is.na(sheet_weight),
                                                             per_unit_value,
                                                             sheet_weight)
                                     ) %>%
                              mutate(per_unit_value_units = ifelse(is.na(sheet_weight),
                                                             per_unit_value_units,
                                                             "lbs per sheet")
                                     ) #%>%
                              #select(-sheet_weight)

rm(separators)
rm(dimension_product)
rm(finding_engineered_wood_thickness)
rm(thickness_calc_extract)
rm(thickness)
```

### Step Three: Quantity calculations

**Create `calculated_quantity` by multiplying the `listed_quantity` by either the `per_unit_value` or the `default_value`:**

With regard to coding and automating, it will be easier to convert most quantities to a consistent calculated value and then do the final conversion to kilograms.

```{r calculated quanitities}
weight_calc <- weight_calc %>% 
   mutate(calculated_quantity = case_when(
      .$deconMaterialName == "dropbox" ~ .$listed_quantity,
      .$default_value_units == "lbs per sq_ft" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "cabinets" & .$listed_units == "count" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "cabinets" & .$listed_units == "lin_ft" ~ .$listed_quantity * .$default_value,
      .$deconMaterialName == "carpeting" ~ .$listed_quantity * .$per_unit_value * .$default_value,
      .$description == "windows" ~ .$listed_quantity * .$default_value,
      .$description == "doors" ~ .$listed_quantity * .$default_value,
      .$per_unit_value_units == "lbs per sheet" ~ .$listed_quantity * .$per_unit_value,
      .$default_value > 0 ~ .$listed_quantity * .$default_value,
      .$per_unit_value > 0 ~ .$listed_quantity * .$per_unit_value,
      .$listed_units == "lbs" ~ .$listed_quantity)
   ) %>%
   mutate(calculated_quantity_units = case_when(
      .$deconMaterialName == "dropbox" ~ .$listed_units,
      .$default_value_units == "lbs per sq_ft" ~ "lbs",
      .$deconMaterialName == "steel product" & .$per_unit_value > 0 ~ .$per_unit_value_units,
      .$deconMaterialName == "steel product" & .$default_value > 0 ~ .$default_value_units,
      .$description == "cabinets" ~ "kg",
      .$description == "doors" ~ "kg",
      .$description == "windows" ~ "kg",
      .$deconMaterialName == "carpeting" ~ "lbs",
      .$per_unit_value_units == "lbs per sheet" ~ "lbs", 
      .$default_value_units == "lbs per bundle" ~ "lbs",
      .$default_value_units == "lbs per fixture" ~ "lbs",
      .$per_unit_value_units == "sq_ft" & .$default_value_units == "lbs per sq_ft" ~ "lbs",
      .$listed_units == "bd_ft" ~ "cu_ft",
      .$per_unit_value_units == "sq_ft" & .$listed_units == "lin_ft" ~ "cu_ft",
      .$per_unit_value_units == "ft thickness" & .$listed_units == "sq_ft" ~ "cu_ft",
      .$deconMaterialName == "hardwood flooring" ~ "cu_ft",
      .$default_value_units == "lbs" ~ .$default_value_units,
      .$listed_units == "lbs" ~ .$listed_units)
   )

# clean up calculated units
calc_unit_cleanup <- str_split_fixed(weight_calc$calculated_quantity_units, "per", n = 3)
weight_calc$calculated_quantity_units <- calc_unit_cleanup[,1]
rm(calc_unit_cleanup)
```


### Step Four: Final conversion to metric weight units

**Assign metric conversion factors to quantity information for the final calculation** 

For this next step, we need to convert material volume (mostly wood products) to weight using material density factors, and convert English weight units to metric. This is accomplished by assigning values from the `unit_conversions` table based on the `calculated_quantity_units` in the `weight_calc` table.
```{r converted quantities}

weight_calc <- weight_calc %>% 
   mutate(converted_quantity = case_when(
      .$calculated_quantity_units == "cu_ft" & .$deconMaterialName == "hardwood flooring" ~ .$calculated_quantity * hardwood_density_factor,
      .$calculated_quantity_units == "cu_ft" & .$deconMaterialName == "softwood lumber" ~ .$calculated_quantity * softwood_density_factor,
      .$calculated_quantity_units == "kg" ~ .$calculated_quantity,
      .$calculated_quantity_units == "lbs" ~ .$calculated_quantity * lbs_to_kg,
      .$calculated_quantity_units == "tons" ~ .$calculated_quantity * short_tons_conversion
   )
   ) %>% 
   mutate(converted_quantity_units = ifelse(is.na(.$converted_quantity), NA, "kg"))

# save for calculation verification, all variables and observations included
write.csv(weight_calc, "intermediary/weight_calc.csv")
```
  
# Final preparations
  
## Document missing/incomplete data

The strategy for handling missing data is dependent upon the source of the missing data. Some missing data have assumption based interpretations, which are documented in the assumptions list. Some incomplete data will have default values substituted such as those carried out in Step Two above. Where possible, average values can be used to impute missing information, but otherwise, missing data in the `listed_quantity` or `listed_units` field will result in the line item being omitted. Missing `description` values are of no consequence, materials with missing descriptions or notes will be included as this field is not an input for the quantification steps. In the following code chunks, the `decon_material_weight` table is checked for missing information. Then these missing data are collected and saved in separate csv files to assist in future tool development and for improving and informing data collection practices. Finally, based on the type of data missing, a determination is made on how the data will be handled and the cleaned and tidy data table of all materials from the deconstruction projects is saved for use in the next R Markdown file.

The following list gives the frequency of missing data by column name
```{r missing data}
countNA <- function(x) { sum(is.na(x)) }
map(select(weight_calc, c("deconMaterialName", "listed_quantity", "listed_units","converted_quantity", "converted_quantity_units") ) , countNA)
```

Because `deconMaterialName` values are entered by the human reading the data from reciepts, missing values happen for a couple different reasons:
* material types not represented in the LCA impact categories data (item is not on the list in the appendix)
* receipt or inventory source does not contain enough information to determine and assign a `deconMaterialName` from the list
Both reasons provide the analyst with different kinds of information: in the first case, this is an opportunity to share information and provide input to DEQ for improving and expanding the material options used in this tool; in the second case, it reinforces information about contractor data collection processes.

Missing `deconMaterialName` values represent line items that presently are material types not represented in the LCA impact categories data. Save missing `deconMaterialName` and optionally email the file to DEQ[^3] to explore adding these materials to the list of LCA material impact factors.

```{r missing impact factor materials}
missing_material_category <- filter(weight_calc, is.na(deconMaterialName)) %>%
   filter(!is.na(listed_quantity) )
write_csv(missing_material_category, "output/missing_deconMaterialName.csv", na = " ")
```

[^3]: Send inquiries and requests for new materials info to `Palmeri.Jordan@deq.state.or.us`

Missing data under `listed_quantity` or `listed_units` represents line items reported by contractors with no detail as to the quantity or other information needed to determine the impacts. Save missing quantity line items to help inform better data collection efforts.

```{r missing units and wt conversions}
missing_quantities <- filter(weight_calc, is.na(converted_quantity))
write_csv(missing_quantities, "output/missing_quantity_data.csv", na = " ")
```

In the City of Portland data, there are a couple problematic projects. There is a note in the raw receipts file for project number 26 that at least one of the dropbox hauler receipts is missing. Additionally, all quantity data is entirely lacking for salvaged materials on project number 28 (only quantity information given was from two dropbox receipts). Note contractor number 6 was the responsible party on both of these projects. This information is helpful for stakeholders, but for now requires that these projects be dropped from the data set. Another trend worth noting after removing projects number 26 and 28 from the `missing_quantities` data is that 36 of the remaining 42 items with missing data come from contractor 1. This could indicate a need for data collection training for these contractors.

A cleaned data table will be saved for use in the next analysis document.
```{r clean tidy material weight data}
decon_material_weight <- weight_calc %>%
   select( "project", "contractor", "house_age", "house_size", "description", "deconMaterialName", "listed_quantity", "listed_units", "dimensions", "dimensional_units", "converted_quantity", "converted_quantity_units") %>%
   filter(.$project != "26") %>%
   filter(.$project != "28") %>%
   drop_na(converted_quantity)
   
write.csv(decon_material_weight, "intermediary/decon_material_weight.csv")
```


## Next Steps

The next step is to prepare the data set that will assign impact factors for the materials. The document `02_decon_data_prep2018.Rmd` contains routines that use the `decon_material_weight` table and other data sources to produce a full table of materials with weights and impacts for the deconstruction scenario.

# Appendix 01

## Table A1 Definitions
```{r definintions table}
kable(definitions)
```

## Table A2 Unit Conversions
```{r unit conversion table}
kable(unit_conversions) 
```

The way to read the table is for each application, starting with the numeric value, move from left to right using the column names between reading the row values. For example: `lumber_USvolume_conversion` has 12 board feet per cubic foot[^1] according to the Google Unit Converter tool. Another example: `metric_softwood_density` has 433.57 kilograms per cubic meter according to the American Wood Council environmental product declaration. To convert from the `per` column unit back to the `value_unit` one must use the reciprocal of the `value` which is given by the `value_reciprocal` column in `per` units. These reciprocal values are then read in reverse from right to left. For example: according to the Google Unit Converter tool there is `r 1/12` (or 1/12) cubic feet in a board foot. Another example using the `value_reciprocal` column would be to read 2.20459 kilograms in the `value_unit` pound. 

[^1]: During the data entry process, special care and attention was given to ensuring that nominal lumber was reported as actual dimensions. All nominal lumber was reported in lineal feet, and is therefore converted to cubic feet volume. All lumber reported in board feet was of the old-growth dimensional type, so correction between nominal dimensions and actual dimensions for board feet is not needed. 

## Table A3 Default Material Dimensions Table 
#### saved in project file as "default_material_dimensions.csv"
```{r default material dimensions table}
kable(default_material_dimensions)
```


